/*
 * Team Project 1: Database Implementation and Use
 * CPSC 315
 *
 * Class: Database
 *
 * The Database class holds a vector of Relations and executes query operations
 * on those Relations.
 *
 * IMPORTANT INPUT FILE INFORMATION
 * The input file must be of the following format:
 *
 * RELATION
   <Name of the relation>
   <Attribute Name> <Attribute Name> <Attribute Name>
   <Tuple>
   <Tuple>
   END RELATION
   RELATION
   etc..
   END RELATION
   0 END
 *
 * This will initialize a relation with the given column names and tuple data.
 *
 * @author Justin Kern, Jill Greczek
 * */

import java.io.File;
import java.io.FileNotFoundException;
import java.util.Scanner;
import java.util.StringTokenizer;
import java.util.Vector;


public class Database {
    
    //EXCEPTIONS
    private Exception NoSuchColumnException = new Exception("Column does not exist.");
    private Exception TooManyColumnsException = new Exception("Too many columns.");
    private Exception RelationNotFoundException = new Exception("Relation not found.");
    private Exception WhereException = new Exception("Where clause failed.");

    //DATA MEMBERS
    private Vector <Relation> relations = new Vector();
    
    //CONSTRUCTORS
    public Database (){
        //Create an empty database
    	relations = new Vector<Relation>();
    }
    
    public Database(String relationName, Vector data, Vector columnNames){
        //Create a relation where 'columnNames' are the names of the attributes
        //and 'data' is what is in the table.
    	Relation newRelation = new Relation(relationName, data, columnNames);
    	relations.add(newRelation);
    }
    
    public Database(File fileName) throws FileNotFoundException{
        //Read in a file and create the table from the file.
        Scanner sc = new Scanner(fileName);
        String currentLine = sc.nextLine();

        while (!currentLine.equals("0 END")){
            Vector columnNames = new Vector();
            Vector <Vector> data = new Vector();
            String name;
            //RELATION denotes the start of new relation data
            if (currentLine.equals("RELATION")){
                name = sc.nextLine();                
                StringTokenizer st = new StringTokenizer(sc.nextLine(), " ");
                while (st.hasMoreTokens()){
                    columnNames.add(st.nextToken());
                }
                
                currentLine = sc.nextLine();
                //END RELATION denotes the end of relation data
                while(!currentLine.equals("END RELATION")){
                    Vector tuple = new Vector();
                    StringTokenizer st2 = new StringTokenizer(currentLine, " ");
                    while(st2.hasMoreTokens()){
                        tuple.add(st2.nextToken());
                    }
                    data.add(tuple);
                    currentLine = sc.nextLine();
                }
                Relation r = new Relation(name, data, columnNames);
                addRelation(r);
            }
            currentLine = sc.nextLine();            
        }
    }
    
    //METHODS
    public void insertTuple(Vector data, String relationName) throws Exception{
        //Add a new tuple to the table.
        relation(relationName).insertTuple(data);
    }
    
    public void addRelation(Relation table){
        //Add an existing relation to the Database
    	relations.add(table);
    }
    
    public void addRelation(String name, Vector data, Vector columnNames){
        //Create a new relation and add it to the Database
    	Relation newRelation = new Relation(name, data, columnNames);
    	relations.add(newRelation);
    }
    
    public void addRelation(File fileName) throws FileNotFoundException{
        //Create a new relation from a file and add it to the Database
        Scanner sc = new Scanner(fileName);
        String currentLine = sc.nextLine();

        while (!currentLine.equals("0 END")){
            Vector columnNames = new Vector();
            Vector <Vector> data = new Vector();
            String name;
            //RELATION denotes the start of new relation data
            if (currentLine.equals("RELATION")){
                name = sc.nextLine();                
                StringTokenizer st = new StringTokenizer(sc.nextLine(), " ");
                while (st.hasMoreTokens()){
                    columnNames.add(st.nextToken());
                }
                
                currentLine = sc.nextLine();
                //END RELATION denotes the end of relation data
                while(!currentLine.equals("END RELATION")){
                    Vector tuple = new Vector();
                    StringTokenizer st2 = new StringTokenizer(currentLine, " ");
                    while(st2.hasMoreTokens()){
                        tuple.add(st2.nextToken());
                    }
                    data.add(tuple);
                    currentLine = sc.nextLine();
                }
                Relation r = new Relation(name, data, columnNames);
                addRelation(r);
            }
            currentLine = sc.nextLine();            
        }
    }
//***** COMBINE ******
    public Relation combine(Relation r1, Relation r2){
        //returns the combination of Relations r1 and r2
        
        for(int i = 0; i < r2.getRowCount(); i++){
            r1.addRow(r2.getRow(i));
        }
        
        return r1;
    }
    
    public Relation query(String s, String f, String w) throws Exception{
        //queries the database in the form SELECT FROM WHERE
        //f is the string after from
        Relation results = from(f);
        //s is the string after select
        results = select(s, results);
        //w is the string after where
        results = where(w, results);
        return results;
       
    }

    public Relation query(String s, String f1, String f2, String w) throws Exception{
        //queries the database for FROM statements with two sources
        //the query is called on two relations and then the answer is combined
        Relation r1 = from(f1);
        Relation r2 = from(f2);

        r1 = select(s, r1);
        r2 = select(s, r2);

        r1 = where(w, r1);
        r2 = where(w, r2);

        return combine(r1,r2);
    }
    
    public Relation from(String relationName) throws Exception{
        //returns the relation specified in the FROM statement
        //if it is not a relation, relation() throws an exception
        return relation(relationName);
    }
    
    public Relation select(String s, Relation r) throws Exception{
        //returns the proper column(s) from relation r
        //if the column does not exist, throws an exception
        if(s.equals("*")){
            //if s is *, just return r unmodified
            return r;
        }
        else{
            Vector <Vector> column = new Vector();
            Vector row = new Vector();
            column.add(row);
            
            //if the column exists that has the name 's' get column data.
            for(int i = 0; i < r.getColumnCount(); i++){
                if(r.getColumnName(i).equals(s)){
                    column.get(0).add(r.getColumn(i));
                }
            }
            //if the column does not exist, throw an exception
            if(column.isEmpty()){
                throw NoSuchColumnException;
            }
            Vector columnName = new Vector();
            columnName.add(s);
            Relation selection = new Relation(s, column, columnName);
            return selection;
        } 
    }
    
    public Relation where(String conditions, Relation r) throws Exception{
        //returns a relation modified by the given conditions
        Scanner sc = new Scanner(conditions);
        String token1 = sc.next();
        
        if (token1.equals("EXISTS")){
            //token2 is the name of the relation that may exist
            String token2 = conditions.substring(token1.length()+1);
            
            if (relation(token2) != null){
                if ((relation(token2).getColumnCount() == 1) && relation(token2).getRowCount() >= 1){
                    return r;
                }else{
                    throw TooManyColumnsException;
                }
            }
        }
        
        else{
            String token2 = sc.next();
            String token3 = sc.next();
            if (token2.equals("IN")){
                //token2 is IN
                //token3 is the other relation
                if(relation(token3) != null){
                    Relation inTable = relation(token3);
                    if (inTable.getColumnCount() == 1){
                        if (inTable.isColumnName(token1) && r.isColumnName(token1)){
                            //obtain the index of the needed columns named "token1"
                            int rColumnIndex = r.getColumnIndex(token1);
                            int inTColumnIndex = inTable.getColumnIndex(token1);

                            Vector rColumnNames = new Vector(); //copy of column names
                            for(int i = 0; i < r.getColumnCount(); i++){
                                rColumnNames.add(r.getColumnName(i));
                            }   
                            Relation answer = new Relation(r.relationName(), new Vector(), rColumnNames);
                            
                            for(int i = 0; i < r.getRowCount(); i++){
                                for(int j = 0; j < inTable.getRowCount(); j++ ){
                                    if((r.getElement(i, rColumnIndex)).equals(inTable.getElement(j, inTColumnIndex))){
                                        answer.insertRow(0, r.getRow(i));
                                    }
                                }
                            }
                           
                            return answer;
                        }else{throw NoSuchColumnException;}//column name not found exception
                    }
                    else{throw TooManyColumnsException;}//number of columns not 1 exception
                }
                else {throw RelationNotFoundException;} //relation not found exception
            }
            else{
                //WHERE has been passed logic operations
                //use class Condition to evaluate the conditions
                Condition c = new Condition(conditions, r);
                return c.getAnswer();
                
            }
        }
        throw WhereException;
    }
    
    
    public Relation relation(String name) throws Exception{
        //Return a relation with the name 'name'.
    	//throws exception if relation is not found.
    	int relSize = relations.size();
    	for(int i = 0; i<relSize; i++){
    		if(relations.elementAt(i).relationName().equals(name)){
    			return relations.elementAt(i);
    		}
    	}
    	throw RelationNotFoundException;
    }
    
    public Relation relation(int i) throws Exception{
        if (i<relations.size()){
            return relations.elementAt(i);
        }
        else throw RelationNotFoundException;
    }
    
    public int numberOfRelations(){
        return relations.size();
    }
            
}
